set ansi_padding, ansi_warnings, concat_null_yields_null, arithabort, quoted_identifier, ansi_nulls on
go

if exists (select * from tempdb..sysobjects where id=object_id('tempdb..#tmpProperties')) drop table #tmpProperties
go

create table #tmpProperties ( name nvarchar(255), value nvarchar(255) )
go

insert into #tmpProperties values('script', 'update.0006.sql')
insert into #tmpProperties values('error', '0')

set xact_abort on
go

set transaction isolation level serializable
go

begin transaction
go

set ansi_nulls on
set quoted_identifier on
go
/* BEGIN UPDATE SCRIPT */


/* Add  DATE_OF_BIRTH, GENDER columns */

alter table dbo.accounts add DATE_OF_BIRTH datetime null
alter table dbo.accounts add GENDER bit null

if object_id('dbo.vwACCOUNTS') is not null drop view dbo.vwACCOUNTS
go
create view dbo.vwACCOUNTS
as
	SELECT     dbo.ACCOUNTS.ID, dbo.ACCOUNTS.ACCOUNT_NUMBER, dbo.ACCOUNTS.NAME, dbo.ACCOUNTS.PHONE_OFFICE, dbo.ACCOUNTS.PHONE_OFFICE AS PHONE, 
						  dbo.ACCOUNTS.PHONE_FAX, dbo.ACCOUNTS.PHONE_ALTERNATE, dbo.ACCOUNTS.WEBSITE, dbo.ACCOUNTS.EMAIL1, dbo.ACCOUNTS.EMAIL2, 
						  dbo.ACCOUNTS.ANNUAL_REVENUE, dbo.ACCOUNTS.EMPLOYEES, dbo.ACCOUNTS.INDUSTRY, dbo.ACCOUNTS.OWNERSHIP, dbo.ACCOUNTS.ACCOUNT_TYPE, 
						  dbo.ACCOUNTS.TICKER_SYMBOL, dbo.ACCOUNTS.RATING, dbo.ACCOUNTS.SIC_CODE, dbo.ACCOUNTS.BILLING_ADDRESS_STREET, 
						  dbo.ACCOUNTS.BILLING_ADDRESS_CITY, dbo.ACCOUNTS.BILLING_ADDRESS_STATE, dbo.ACCOUNTS.BILLING_ADDRESS_POSTALCODE, 
						  dbo.ACCOUNTS.BILLING_ADDRESS_COUNTRY, dbo.fnLocation(dbo.ACCOUNTS.BILLING_ADDRESS_CITY, dbo.ACCOUNTS.BILLING_ADDRESS_STATE) AS CITY, 
						  dbo.ACCOUNTS.SHIPPING_ADDRESS_STREET, dbo.ACCOUNTS.SHIPPING_ADDRESS_CITY, dbo.ACCOUNTS.SHIPPING_ADDRESS_STATE, 
						  dbo.ACCOUNTS.SHIPPING_ADDRESS_POSTALCODE, dbo.ACCOUNTS.SHIPPING_ADDRESS_COUNTRY, dbo.ACCOUNTS.ASSIGNED_USER_ID, 
						  dbo.ACCOUNTS.PARENT_ID, ACCOUNTS_PARENT.NAME AS PARENT_NAME, dbo.ACCOUNTS.DATE_ENTERED, dbo.ACCOUNTS.DATE_MODIFIED, 
						  dbo.ACCOUNTS.DATE_MODIFIED_UTC, dbo.ACCOUNTS.DESCRIPTION, dbo.TEAMS.ID AS TEAM_ID, dbo.TEAMS.NAME AS TEAM_NAME, 
						  USERS_ASSIGNED.USER_NAME AS ASSIGNED_TO, USERS_CREATED_BY.USER_NAME AS CREATED_BY, USERS_MODIFIED_BY.USER_NAME AS MODIFIED_BY, 
						  dbo.ACCOUNTS.CREATED_BY AS CREATED_BY_ID, dbo.ACCOUNTS.MODIFIED_USER_ID, dbo.TEAM_SETS.ID AS TEAM_SET_ID, dbo.TEAM_SETS.TEAM_SET_NAME, 
						  dbo.TEAM_SETS.TEAM_SET_LIST, dbo.fnFullName(USERS_ASSIGNED.FIRST_NAME, USERS_ASSIGNED.LAST_NAME) AS ASSIGNED_TO_NAME, 
						  dbo.fnFullName(USERS_CREATED_BY.FIRST_NAME, USERS_CREATED_BY.LAST_NAME) AS CREATED_BY_NAME, 
						  dbo.fnFullName(USERS_MODIFIED_BY.FIRST_NAME, USERS_MODIFIED_BY.LAST_NAME) AS MODIFIED_BY_NAME, dbo.ACCOUNTS_CSTM.ID_C,
						  dbo.ACCOUNTS.DATE_OF_BIRTH, dbo.ACCOUNTS.GENDER
	FROM         dbo.ACCOUNTS LEFT OUTER JOIN
						  dbo.ACCOUNTS AS ACCOUNTS_PARENT ON ACCOUNTS_PARENT.ID = dbo.ACCOUNTS.PARENT_ID AND ACCOUNTS_PARENT.DELETED = 0 LEFT OUTER JOIN
						  dbo.TEAMS ON dbo.TEAMS.ID = dbo.ACCOUNTS.TEAM_ID AND dbo.TEAMS.DELETED = 0 LEFT OUTER JOIN
						  dbo.TEAM_SETS ON dbo.TEAM_SETS.ID = dbo.ACCOUNTS.TEAM_SET_ID AND dbo.TEAM_SETS.DELETED = 0 LEFT OUTER JOIN
						  dbo.USERS AS USERS_ASSIGNED ON USERS_ASSIGNED.ID = dbo.ACCOUNTS.ASSIGNED_USER_ID LEFT OUTER JOIN
						  dbo.USERS AS USERS_CREATED_BY ON USERS_CREATED_BY.ID = dbo.ACCOUNTS.CREATED_BY LEFT OUTER JOIN
						  dbo.USERS AS USERS_MODIFIED_BY ON USERS_MODIFIED_BY.ID = dbo.ACCOUNTS.MODIFIED_USER_ID LEFT OUTER JOIN
						  dbo.ACCOUNTS_CSTM ON dbo.ACCOUNTS_CSTM.ID_C = dbo.ACCOUNTS.ID
	WHERE     (dbo.ACCOUNTS.DELETED = 0)
go

/* END UPDATE SCRIPT */
go
if @@error<>0 and @@trancount>0 rollback transaction
go

if @@trancount=0 begin
	update #tmpProperties set value = 1 where name = 'error'
	begin transaction
end
go

if (select top 1 value from #tmpProperties where name = 'error') <> '0' rollback transaction
go

if @@trancount > 0 begin
	declare @@scriptName nvarchar(255)
	select top 1 @@scriptName = value from #tmpProperties where name = 'script'

	if dbo.scriptExisted(@@scriptName) = 1 begin
		rollback transaction
		print @@scriptName + ' already executes'
	end
	else begin
		exec dbo.updateScript @@scriptName
		commit transaction
		print 'The database update succeeded'
	end
END
else begin
	print 'The database update failed'
end
go

drop table #tmpProperties
go
